package org.goobs.database;

import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class Database {
	private static final int MYSQL	=	1;
	private static final int SQLITE	=	2;
	
	private static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver"; // MySQL MM JDBC driver
	private static final String SQLITE_DRIVER = "org.sqlite.JDBC";

	private int type;
	private String server, username, password, schema;
	private File sqlite;
	private Connection conn = null;
	private Statement lastStatement;
	

	public Database(File sqlite){
		this.sqlite = sqlite;
		this.schema = schema;
		this.type = SQLITE;
	}
	
	public Database(String server, String username, String password,
			String schema) {
		this.server = server;
		this.username = username;
		this.password = password;
		this.schema = schema;
		this.type = MYSQL;
	}

	public boolean connect() {
		try {
			if(type == MYSQL){
				//(MySQL)
				Class.forName(MYSQL_DRIVER);
				String url = "jdbc:mysql://" + server + "/" + schema; // a JDBC url
				conn = DriverManager.getConnection(url, username, password);
			}else if(type == SQLITE){
				//(SqLite)
				System.setProperty("sqlite.purejava", "true");
				Class.forName(SQLITE_DRIVER);
				conn = DriverManager.getConnection("jdbc:sqlite:" + sqlite.getAbsolutePath());
			}else{
				return false;
			}
		} catch (ClassNotFoundException e) {
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public String[] getTableNames() {
		if (conn == null) {
			return null;
		}
		try {
			// Gets the database metadata
			DatabaseMetaData dbmd = conn.getMetaData();

			// Specify the type of object; in this case we want tables
			String[] types = { "TABLE" };
			ResultSet resultSet = dbmd.getTables(null, schema, "%", types);

			// Get the table names
			ArrayList<String> rtnLst = new ArrayList<String>();
			while (resultSet.next()) {
				// Get the table name
				rtnLst.add(resultSet.getString(3));
			}
			String[] rtn = new String[rtnLst.size()];
			for (int i = 0; i < rtnLst.size(); i++) {
				rtn[i] = rtnLst.get(i);
			}
			return rtn;
		} catch (SQLException e) {
			return null;
		}
	}

	public Table[] getTables() {
		String[] names = getTableNames();
		Table[] rtn = new Table[names.length];
		for (int i = 0; i < names.length; i++) {
			rtn[i] = new Table(names[i], this);
		}
		return rtn;
	}

	public Table getTable(String name) {
		String[] tables = getTableNames();
		if(tables == null){
			return null;
		}
		for (String t : tables) {
			if (t.equalsIgnoreCase(name)) {
				return new Table(t, this);
			}
		}
		return null;

	}

	public boolean createTable(String name, String[] names, String[] types,
			boolean force) {
		return createTable(name, names, types, new String[0], force);
	}

	public boolean createTable(String name, String[] names, String[] types,
			String[] indexes, boolean force) {

		// --Argument Check
		if (names.length != types.length) {
			return false;
		}
		// --Prepare statement
		StringBuilder b = new StringBuilder();
		String ne = "";
		if (!force) {
			ne = " IF NOT EXISTS ";
		}
		// (overhead)
		b.append("CREATE TABLE ").append(ne).append(" `").append(schema)
				.append("`.`").append(name).append("` (\n");
		b.append("\t`id` INT NOT NULL AUTO_INCREMENT,\n");
		// (columns)
		for (int i = 0; i < names.length; i++) {
			if (names[i].equalsIgnoreCase("id")) {
				continue;
			} else {
				b.append("\t`").append(names[i]).append("` ").append(types[i])
						.append(",\n");
			}
		}
		// (indexes)
		for (int i = 0; i < indexes.length; i++) {
			b.append("\tINDEX (").append(indexes[i]).append("),\n");
		}
		b.append("\tPRIMARY KEY (`id`)\n)");
		// --Execute statement
		String query = b.toString();
		return runQuery(query);
	}

	public ResultFactory join(Table a, Table b, String condition) {
		return join(a, b, condition, null);
	}
	
	public ResultFactory join(Table a, Table b, String condition,
			String[] columns) {
		String strA = a.getName();
		String strB = b.getName();

		String cols = "*";
		
		if(columns != null && columns.length > 0){
			StringBuilder build = new StringBuilder();
			for (String col : columns) {
				build.append(col).append(", ");
			}
			cols = build.toString().substring(0,
					Math.max(0, build.length() - 2));
			if (cols.trim().equals("")) {
				cols = "*";
			}
		}

		StringBuilder build = new StringBuilder();
		build.append("SELECT ").append(cols).append(" FROM ").append(strA)
				.append(" JOIN ").append(strB).append(" ON ").append(condition);
		
		if(!runQuery(build.toString())){
			return null;
		}else{
			return new ResultFactory(getLastStatementResult(), getLastStatementMetaData(), null);
		}

	}

	public boolean runQuery(String query) {
		return runQuery(query, false);
	}

	public boolean runQuery(String query, boolean sanitize) {
		try {
			if (sanitize) {
				query = sanitize(query);
			}
			lastStatement = conn.createStatement();
			// Execute the insert statement
//			System.out.println(query);
			boolean rtn = lastStatement.execute(query);
			return rtn;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public ResultSet getLastStatementResult() {
		if (lastStatement == null) {
			return null;
		}
		try {
			return lastStatement.getResultSet();
		} catch (SQLException e) {
			return null;
		}
	}

	public ResultSetMetaData getLastStatementMetaData() {
		ResultSet rs = getLastStatementResult();
		if (rs != null) {
			try {
				return rs.getMetaData();
			} catch (SQLException e) {
				return null;
			}
		} else {
			return null;
		}
	}

	public static String sanitize(String query) {
		query = query.replaceAll("'", "\\\\'");
		return query;
	}

	public static void main(String[] args) {
		File f = new File("/home/gabor/staging/cookies.sqlite");
		Database sql = new Database(f);
		System.out.println(sql.connect());
	}
}
